﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class WBFMOVData : BaseFunction
    {
        public bool flagstartmtr;
        public bool flagRegenerateLastMonthData;
        string InvDate;
        string LASTINVDATE;
        ArrayList ListWBInventory;
        ArrayList ListTris = new ArrayList();
        string fileWBDATINV = @"C:\intex.w\Report\Warehouse\WBDATINV.xml";
        DataSet setWBDATINV = new DataSet();
        DataTable tbWBDATINV;
        List<WBMOVEMENT> ListWBMOVEMENT;
        List<WGSummary> ListWGSummary;
        string TrisInclude = "";
        public int ThreadCount;
        private string OrderNum;
        DataTable tbWBMOVEMENT;
        public WBFMOVData()
            : base()
        {
            BuildTrisList();
            tbWBMOVEMENT = new DataTable("ICC_WBMOVSUM");
            tbWBMOVEMENT.Columns.Add("ICCWBMDAT", typeof(double));
            tbWBMOVEMENT.Columns.Add("ICCWBMTYPE", typeof(string));
            tbWBMOVEMENT.Columns.Add("ICCWBMWAKZ", typeof(string));
            tbWBMOVEMENT.Columns.Add("ICCWBMBS", typeof(string));
            tbWBMOVEMENT.Columns.Add("ICCWBMBELEG", typeof(int));
            tbWBMOVEMENT.Columns.Add("ARTICLE", typeof(string));
            tbWBMOVEMENT.Columns.Add("ICCWBMBESCHICH", typeof(string));
            tbWBMOVEMENT.Columns.Add("ICCWBMDPART", typeof(string));
            tbWBMOVEMENT.Columns.Add("ICCWBMCODE", typeof(string));
            tbWBMOVEMENT.Columns.Add("ICCWBMNAME", typeof(string));
            tbWBMOVEMENT.Columns.Add("ICCWBMBDES", typeof(string));
            tbWBMOVEMENT.Columns.Add("ICCWBMAUFTYP", typeof(string));
            tbWBMOVEMENT.Columns.Add("ORDERNO", typeof(string));
            tbWBMOVEMENT.Columns.Add("STARTMTR", typeof(double));
            tbWBMOVEMENT.Columns.Add("STARTPCNUM", typeof(int));
            tbWBMOVEMENT.Columns.Add("INMTR", typeof(double));
            tbWBMOVEMENT.Columns.Add("INPCNUM", typeof(int));
            tbWBMOVEMENT.Columns.Add("OUTMTR", typeof(double));
            tbWBMOVEMENT.Columns.Add("OUTPCNUM", typeof(int));
            tbWBMOVEMENT.Columns.Add("ADJMTR", typeof(double));
            tbWBMOVEMENT.Columns.Add("ENDMTR", typeof(double));
            tbWBMOVEMENT.Columns.Add("ENDPCNUM", typeof(int));
            setWBDATINV.Merge(tbWBMOVEMENT);

        }
        public WBFMOVData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }
        private void BuildTrisList()
        {
            ListTris = new ArrayList();
            TrisType tristype = null;
            TrisInclude = "01;10;11;13;";
            tristype = new TrisType("01", "+", "净长"); ListTris.Add(tristype);
            tristype = new TrisType("10", "+", "货物收入"); ListTris.Add(tristype);
            tristype = new TrisType("11", "+", "货物收入"); ListTris.Add(tristype);
            tristype = new TrisType("12", "-", "销售返回"); ListTris.Add(tristype);
            tristype = new TrisType("13", "+", "产品录入"); ListTris.Add(tristype);
            tristype = new TrisType("20", "-", "货物发出"); ListTris.Add(tristype);
            tristype = new TrisType("21", "+", "货物进入"); ListTris.Add(tristype);
            tristype = new TrisType("26", "-", "旧布卷减少"); ListTris.Add(tristype);
            tristype = new TrisType("27", "+", "新布卷增加"); ListTris.Add(tristype);
            tristype = new TrisType("31", "-", "发货"); ListTris.Add(tristype);
            tristype = new TrisType("32", "+", "退货"); ListTris.Add(tristype);
            tristype = new TrisType("36", "-", "生产消耗"); ListTris.Add(tristype);
            tristype = new TrisType("40", "-", "等级修改减少"); ListTris.Add(tristype);
            tristype = new TrisType("41", "-", "等级修改增加"); ListTris.Add(tristype);
            tristype = new TrisType("42", "+", "库存修正减少"); ListTris.Add(tristype);
            tristype = new TrisType("43", "+", "库存修正增加"); ListTris.Add(tristype);
            tristype = new TrisType("48", "-", "生产差异-"); ListTris.Add(tristype);
            tristype = new TrisType("49", "+", "生产差异+"); ListTris.Add(tristype);
            tristype = new TrisType("51", "+", "消耗差异"); ListTris.Add(tristype);
            tristype = new TrisType("61", "-", "留位"); ListTris.Add(tristype);
            tristype = new TrisType("62", "+", "取消留位"); ListTris.Add(tristype);
            tristype = new TrisType("64", "+", "留位剪布"); ListTris.Add(tristype);
            tristype = new TrisType("65", "+", "锁定"); ListTris.Add(tristype);
            tristype = new TrisType("66", "+", "解锁"); ListTris.Add(tristype);
            tristype = new TrisType("69", "+", "其他修正"); ListTris.Add(tristype);
            tristype = new TrisType("80", "+", "发票数量"); ListTris.Add(tristype);
            tristype = new TrisType("88", "-", "价值更改-"); ListTris.Add(tristype);
            tristype = new TrisType("89", "+", "价值更改"); ListTris.Add(tristype);
            tristype = new TrisType("IN", "+", "系统自动调节"); ListTris.Add(tristype);

        }
        #region  SUN_CODE
        public DataSet GetRawData(DataRow dwABPOS, string type = "Sun")
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            string VKPAUFNUM;
            string WBSRTDATE, WBENTDATE;
            WBSRTDATE = dwABPOS["WBSRTDATE"].ToString().Trim();
            WBENTDATE = dwABPOS["WBENTDATE"].ToString().Trim();
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand();
                #region
                /*
                 SELECT TRIM(WBEWART)||'-'||TRIM(WBEWFARBE)||'-'||TRIM(WBEWVAR) AS ARTICLE,WBEWBUCHDAT,WBEWBS,WBBDPART, COUNT(WBEWLFDNR)AS PCNUM,WBEWLO,WBEWLB,SUM(WBEWMTR)AS WBEWMTR,0 as STARTQTY,0 as ENDQTY,0 as STARTPCNUM,0 as ENDPCNUM FROM WBBEW W JOIN WBBST B ON WBEWSTKNR=WBBSTKNR WHERE WBEWWAKZ='R' AND WBEWBUCHDAT>'20110401' AND WBEWBUCHDAT<'20110501' GROUP BY WBEWLO,WBEWLB,WBEWART,WBEWFARBE,WBEWVAR,WBEWBUCHDAT,WBEWBS,WBBDPART ORDER BY WBEWBUCHDAT,ARTICLE
                 WBEWLB
                 022002-3-A1
                 */
                or1.CommandText = "SELECT TRIM(WBEWART)||'-'||TRIM(WBEWFARBE)||'-'||TRIM(WBEWVAR) AS ARTICLE"
                    + ",WBEWBUCHDAT,WBEWBS,' ' AS FUNCTION,WBBDPART, COUNT(WBEWSTKNR)AS PCNUM,WBEWLO,"
                    + "SUM(WBEWMTR)*0.001 AS WBEWMTR,0 AS STARTQTY,0 AS ENDQTY,0 AS STARTPCNUM,0 AS ENDPCNUM"
                    + ",MAX(WBEWBELEG) AS WBEWBELEG"
                    + " FROM WBBEW W JOIN WBBST B ON WBEWSTKNR=WBBSTKNR"
                    + " WHERE WBEWWAKZ='R' AND WBEWBUCHDAT>='" + WBSRTDATE + "' AND WBEWBUCHDAT<='" + WBENTDATE + "'"
                    //+ " AND WBBART LIKE '022002%' AND WBBVAR='A1' AND WBBDPART LIKE '04100%'"
                    + " GROUP BY WBEWLO,WBEWART,WBEWFARBE,WBEWVAR,WBEWBUCHDAT,WBEWBS,WBBDPART"
                    + " ORDER BY ARTICLE,WBEWBUCHDAT, WBEWBELEG"
                    ;
                #endregion
                setLocal = Connect2Proxy(or1);
                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "WBBEW";
                    SetDefault(tableLocal);
                    byte[] b;
                    string value;
                    string GISSUEDATE = "", sGISSUEDATE;
                    decimal GMETER = 0, sGMETER;
                    decimal GPCNUM = 0, sGPCNUM;
                    string sTemp = "";
                    string SIZEBATCHNO = "";
                    string VKPWUTERM = "";
                    int Year, Month, Day;
                    string Column = "";
                    double VKPMENGE1 = 0, FACTLENG = 0, FinishedLength = 0;
                    string ARTICLE = "";
                    double InitialQty = 0;
                    double Qty = 0;
                    double balance = 0;
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "ARTICLE";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "WBBDPART";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        BalanceQty(dwTemp);
                    }
                    setLocal.AcceptChanges();
                    set4Return.Merge(setLocal);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }
        private void BalanceQty(DataRow dwWBBEW, string type = "Sun")
        {
            string direction = "+";
            string WBEWBS = dwWBBEW["WBEWBS"].ToString().Trim();
            if (!TrisInclude.Contains(WBEWBS + ";"))
            {
                dwWBBEW.Delete();
                return;
            }
            var queryX = from TrisType tris in ListTris
                         where tris.TrisCode == WBEWBS
                         select tris;
            foreach (TrisType tris in queryX)
            {
                direction = tris.TrisDirect;
                dwWBBEW["FUNCTION"] = tris.Description;
            }
            string ARTICLE = dwWBBEW["ARTICLE"].ToString().Trim();
            string WBBLO = dwWBBEW["WBEWLO"].ToString().Trim();
            string WBBDPART = dwWBBEW["WBBDPART"].ToString().Trim();
            double INVQTYMTR = 0, QtyMTR = 0;
            double balance = 0;
            int INVPCNUM = 0, QtyPCNum = 0;
            WBInventory wbInv = null;
            string expression;
            try
            {
                var query1 = from WBInventory wbinv in ListWBInventory
                             where wbinv.INVDATE == LASTINVDATE
                                   && wbinv.Article == ARTICLE
                                   && wbinv.WBBDPART == WBBDPART
                             select wbinv;
                foreach (WBInventory wbinv in query1)
                {
                    wbInv = wbinv;
                }
                if (wbInv == null)
                {
                    wbInv = new WBInventory(LASTINVDATE, WBBLO, ARTICLE, WBBDPART, 0, 0);
                    ListWBInventory.Add(wbInv);
                }
                QtyMTR = System.Math.Round(System.Convert.ToDouble(dwWBBEW["WBEWMTR"].ToString()) * 100 * 0.01, 2);
                QtyPCNum = System.Convert.ToInt16(dwWBBEW["PCNUM"].ToString());
                if (direction == "-")
                {
                    QtyMTR = 0 - QtyMTR;
                    QtyPCNum = 0 - QtyPCNum;
                    dwWBBEW["WBEWMTR"] = QtyMTR;
                    dwWBBEW["PCNUM"] = QtyPCNum;
                }
                dwWBBEW["STARTQTY"] = System.Math.Round(wbInv.lastQtyMtr, 2);
                dwWBBEW["STARTPCNUM"] = wbInv.LastPcNum;
                if (flagRegenerateLastMonthData)
                {
                    wbInv.lastQtyMtr -= QtyMTR;
                    wbInv.LastPcNum -= QtyPCNum;
                }
                else
                {
                    wbInv.lastQtyMtr += QtyMTR;
                    wbInv.LastPcNum += QtyPCNum;
                }
                dwWBBEW["ENDQTY"] = System.Math.Round(wbInv.lastQtyMtr);
                dwWBBEW["ENDPCNUM"] = wbInv.LastPcNum;
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        public void LoadINVQty()
        {
            try
            {
                DataRow dwWBDATINV = null;
                setWBDATINV.Clear();
                DataSet setResult = new DataSet();
                DataTable tableLocal = null;
                if (System.IO.File.Exists(fileWBDATINV))
                {
                    setResult.ReadXml(fileWBDATINV);

                    tbWBDATINV = setWBDATINV.Tables["WBDATINV"];
                    if (setResult.Tables.Contains("WBDATINV"))
                    {
                        foreach (DataRow dwTemp in setResult.Tables["WBDATINV"].Rows)
                        {
                            dwWBDATINV = tbWBDATINV.NewRow();
                            dwWBDATINV.ItemArray = dwTemp.ItemArray;
                            tbWBDATINV.Rows.Add(dwWBDATINV);
                        }
                    }
                }
                if (flagRegenerateLastMonthData)
                {
                    #region ClearCurrentINVDATE data
                    tbWBDATINV = setWBDATINV.Tables["WBDATINV"];
                    if (tbWBDATINV != null)
                    {
                        var queryX = from DataRow dwTemp in tbWBDATINV.AsEnumerable()
                                     where dwTemp["INVDATE"].ToString() == LASTINVDATE
                                     select dwTemp;
                        List<DataRow> deletedRows = new List<DataRow>();
                        foreach (DataRow dwTemp in queryX)
                        {
                            deletedRows.Add(dwTemp);
                        }
                        foreach (DataRow dwTemp in deletedRows)
                        {
                            dwTemp.Delete();
                        }
                        tbWBDATINV.AcceptChanges();
                    }
                    #endregion
                    #region Get CurrentStock
                    OracleCommand or1 = OracleDB.CreateCommand();
                    or1.CommandText = "SELECT DISTINCT '" + LASTINVDATE + "' AS INVDATE,WBBLO,TRIM(WBBART)||'-'||TRIM(WBBFARBE)||'-'||TRIM(WBBVAR) AS ARTICLE"
               + ",WBBDPART, COUNT(*)AS INVPCNUM,SUM(WBBSSTKLG)*0.001 AS INVQTYMTR FROM  WBBST B WHERE WBBWAKZ='R'"
                        //+ " AND WBBART LIKE '022002%' AND WBBVAR='A1' AND WBBDPART LIKE '04100%'"
               + "GROUP BY WBBLO,WBBART,WBBFARBE,WBBVAR,WBBDPART ORDER BY ARTICLE";

                    setResult = Connect2Proxy(or1);
                    if (setResult.Tables.Count > 0)
                    {
                        tableLocal = setResult.Tables[0];
                        tableLocal.TableName = "WBDATINV";
                        string Column = "";
                        byte[] b;
                        string value;

                        foreach (DataRow dwTemp in tableLocal.Rows)
                        {
                            Column = "ARTICLE";
                            #region
                            b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                            value = Encoding.GetEncoding("gb2312").GetString(b);
                            dwTemp[Column] = value;
                            #endregion
                            Column = "WBBDPART";
                            #region
                            b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                            value = Encoding.GetEncoding("gb2312").GetString(b);
                            dwTemp[Column] = value;
                            #endregion
                            dwWBDATINV = tbWBDATINV.NewRow();
                            dwWBDATINV.ItemArray = dwTemp.ItemArray;
                            tbWBDATINV.Rows.Add(dwWBDATINV);
                        }
                    }
                    #endregion
                }
                #region Build ListWBInventory
                if (setWBDATINV.Tables.Contains("WBDATINV"))
                {
                    tbWBDATINV = setWBDATINV.Tables["WBDATINV"];
                    string ARTICLE, WBBDPART, WBBLO;
                    string lastInvDate;
                    WBInventory wbInv;
                    int INVPCNUM;
                    double INVQTYMTR;
                    ListWBInventory = new ArrayList();
                    foreach (DataRow dwTemp in tbWBDATINV.Rows)
                    {
                        ARTICLE = dwTemp["ARTICLE"].ToString();
                        WBBDPART = dwTemp["WBBDPART"].ToString();
                        WBBLO = dwTemp["WBBLO"].ToString();
                        lastInvDate = dwTemp["INVDATE"].ToString();
                        INVQTYMTR = System.Math.Round(System.Convert.ToDouble(dwTemp["INVQTYMTR"].ToString()), 2);
                        INVPCNUM = System.Convert.ToInt16(dwTemp["INVPCNUM"].ToString());
                        wbInv = new WBInventory(lastInvDate, WBBLO, ARTICLE, WBBDPART, INVPCNUM, INVQTYMTR);
                        ListWBInventory.Add(wbInv);
                    }
                }
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }

        }
        public void SaveAsInv()
        {
            DataRow dwINV = null;
            dwINV = tbWBDATINV.NewRow();
            bool flagExist = false;
            foreach (WBInventory wbinv in ListWBInventory)
            {
                flagExist = false;
                var query = from DataRow dwInv in tbWBDATINV.AsEnumerable()
                            where dwInv["INVDATE"] == InvDate
                            && dwInv["ARTICLE"] == wbinv.Article
                            && dwInv["WBBLO"] == wbinv.WBBLO
                            && dwInv["WBBDPART"] == wbinv.WBBDPART
                            select dwInv;
                foreach (DataRow dwTemp in query)
                {
                    dwINV = dwTemp;
                    flagExist = true;
                }
                if (!flagExist)
                {
                    dwINV = tbWBDATINV.NewRow();
                }
                dwINV["INVDATE"] = InvDate;
                dwINV["ARTICLE"] = wbinv.Article;
                dwINV["WBBLO"] = wbinv.WBBLO;
                dwINV["WBBDPART"] = wbinv.WBBDPART;
                dwINV["INVPCNUM"] = wbinv.LastPcNum;
                dwINV["INVQTYMTR"] = System.Math.Round(wbinv.lastQtyMtr, 2);
                if (!flagExist)
                {
                    tbWBDATINV.Rows.Add(dwINV);
                }
            }
            if (System.IO.File.Exists(fileWBDATINV))
            {
                System.IO.File.Delete(fileWBDATINV);
            }
            setWBDATINV.WriteXml(fileWBDATINV);
        }
        public void SaveINVQty()
        {
            if (!flagRegenerateLastMonthData)
            {
                return;
            }
            DataRow dwINV = null;

            dwINV = tbWBDATINV.NewRow();
            bool flagExist = false;
            foreach (WBInventory wbinv in ListWBInventory)
            {
                flagExist = false;
                var query = from DataRow dwInv in tbWBDATINV.AsEnumerable()
                            where dwInv["INVDATE"] == wbinv.INVDATE
                            && dwInv["ARTICLE"] == wbinv.Article
                            && dwInv["WBBLO"] == wbinv.WBBLO
                            && dwInv["WBBDPART"] == wbinv.WBBDPART
                            select dwInv;
                foreach (DataRow dwTemp in query)
                {
                    dwINV = dwTemp;
                    flagExist = true;
                }
                if (!flagExist)
                {
                    dwINV = tbWBDATINV.NewRow();
                }
                dwINV["INVDATE"] = wbinv.INVDATE;
                dwINV["ARTICLE"] = wbinv.Article;
                dwINV["WBBLO"] = wbinv.WBBLO;
                dwINV["WBBDPART"] = wbinv.WBBDPART;
                dwINV["INVPCNUM"] = wbinv.LastPcNum;
                dwINV["INVQTYMTR"] = System.Math.Round(wbinv.lastQtyMtr, 2);
                if (!flagExist)
                {
                    tbWBDATINV.Rows.Add(dwINV);
                }
            }
            if (System.IO.File.Exists(fileWBDATINV))
            {
                System.IO.File.Delete(fileWBDATINV);
            }
            setWBDATINV.WriteXml(fileWBDATINV);
        }
        public DataSet BuildWBBST(DataRow dwUI, string type = "Sun")
        {
            string progress = "查询完成";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            InvDate = dwUI["INVDATE"].ToString();
            LASTINVDATE = dwUI["LASTINVDATE"].ToString();
            try
            {
                EventDispatcher.Instance.RaiseStatus("查询开始", StatusEventType.StartProgress);

                EventDispatcher.Instance.RaiseStatus("装载结存表", StatusEventType.RunningProgress);
                LoadINVQty();
                tbWBDATINV = setWBDATINV.Tables["WBDATINV"];
                EventDispatcher.Instance.RaiseStatus("装载出入库记录", StatusEventType.RunningProgress);
                setResult = GetRawData(dwUI);
                EventDispatcher.Instance.RaiseStatus("计算结存", StatusEventType.RunningProgress);
                setReturn.Merge(setResult);
                EventDispatcher.Instance.RaiseStatus("保存结存表", StatusEventType.RunningProgress);
                SaveINVQty();
                setReturn.Merge(setWBDATINV);
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            return setReturn;
        }
        #endregion
        public DataSet LoadICC_WBBST(DataRow dwABPOS)
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            string VKPAUFNUM;
            string WBSRTDATE, WBENTDATE, INVDATE;
            WBSRTDATE = dwABPOS["WBSRTDATE"].ToString().Trim();
            WBENTDATE = dwABPOS["WBENTDATE"].ToString().Trim();
            INVDATE = GetDateAddDays(WBSRTDATE,-1);

            try
            {
                #region ==============GET the INV Data
                OracleCommand or1 = OracleDB.CreateCommand();
                #region

                or1.CommandText = "select A.ICCWBBDAT AS ICCWBMDAT,'' AS ICCWBMTYPE,A.ICCWBBWAKZ AS ICCWBMWAKZ, "
                    + " '' AS ICCWBMBS,0 AS ICCWBMBELEG,TRIM(A.ICCWBBART)||'-'||TRIM(A.ICCWBBFARBE)||'-'||A.ICCWBBVAR AS ARTICLE, "
                    + " A.ICCWBBBESCHICH AS ICCWBMBESCHICH,A.ICCWBBDPART AS ICCWBMDPART,'' AS ICCWBMCODE, "
                    + " '' AS ICCWBMNAME,'' AS ICCWBMBDES,'' AS ICCWBMAUFTYP,'' AS ORDERNO,ROUND(SUM(A.ICCWBBISTKLG/1000/0.9144),1) AS STARTMTR, "
                    + " COUNT(A.ICCWBBSTKNR) AS STARTPCNUM,0 AS INMTR,0 AS INPCNUM,0 AS OUTMTR,0 AS OUTPCNUM,0 AS ADJMTR, "
                    + " ROUND(SUM(A.ICCWBBISTKLG/1000/0.9144),1) AS ENDMTR,COUNT(A.ICCWBBSTKNR) AS ENDPCNUM "
                    + " from icc_wbbst A where A.ICCWBBDAT = '" + INVDATE + "'"
                    + " AND A.ICCWBBWAKZ = 'F' AND A.ICCWBBLB <> 'HZ' "
                    + " GROUP BY A.ICCWBBDAT,A.ICCWBBWAKZ,TRIM(A.ICCWBBART)||'-'||TRIM(A.ICCWBBFARBE)||'-'||A.ICCWBBVAR, "
                    + " A.ICCWBBBESCHICH,A.ICCWBBDPART "
                     ;
                #endregion
                setLocal = Connect2Proxy(or1);
                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "ICC_WBBST";
                    SetDefault(tableLocal);
                    byte[] b;
                    string value;
                    string Column = "";
                    DataRow dwWBMOVEMENT = null;
                    List<DataRow> dltRows = new List<DataRow>();
                    ListWBMOVEMENT = new List<WBMOVEMENT>();
                    tbWBMOVEMENT = setWBDATINV.Tables["ICC_WBMOVSUM"];
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "ARTICLE";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "ICCWBMBESCHICH";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "ICCWBMDPART";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        dwWBMOVEMENT = tbWBMOVEMENT.NewRow();
                        dwWBMOVEMENT.ItemArray = dwTemp.ItemArray;
                        tbWBMOVEMENT.Rows.Add(dwWBMOVEMENT);
                    }
                }
                #endregion
                #region Build ListWBInventory 
                string ARTICLE, WBBDPART, WBBLO, ICCWBBWAKZ;
                string lastInvDate;
                string FINISH;
                WBInventory wbInv;
                int INVPCNUM;
                double INVQTYMTR;
                ListWBInventory = new ArrayList();
                foreach (DataRow dwTemp in tbWBMOVEMENT.Rows)
                {
                    ARTICLE = dwTemp["ARTICLE"].ToString().Trim();
                    FINISH = dwTemp["ICCWBMBESCHICH"].ToString().Trim();
                    WBBDPART = dwTemp["ICCWBMDPART"].ToString().Trim();
                    ICCWBBWAKZ = dwTemp["ICCWBMWAKZ"].ToString().Trim();
                    
                    WBBLO = "HL";// dwTemp["WBBLO"].ToString();
                    lastInvDate = dwTemp["ICCWBMDAT"].ToString();
                    INVQTYMTR = System.Math.Round(System.Convert.ToDouble(dwTemp["STARTMTR"].ToString()), 1);
                    INVPCNUM = System.Convert.ToInt16(dwTemp["STARTPCNUM"].ToString());
                    wbInv = new WBInventory(lastInvDate, WBBLO, ICCWBBWAKZ,ARTICLE, FINISH, WBBDPART, INVPCNUM, INVQTYMTR);
                    ListWBInventory.Add(wbInv);
                } 
                #endregion
                set4Return.Merge(setLocal);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }
        public DataSet GetRawData(DataRow dwABPOS)
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            string VKPAUFNUM;
            string WBSRTDATE, WBENTDATE;
            WBSRTDATE = dwABPOS["WBSRTDATE"].ToString().Trim();
            WBENTDATE = dwABPOS["WBENTDATE"].ToString().Trim();
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand();
                #region
                /*
                
                */
                or1.CommandText = "select A.ICCWBBDAT AS ICCWBMDAT,'AAA' AS ICCWBMTYPE,A.ICCWBBWAKZ AS ICCWBMWAKZ, "
                    + " '' AS ICCWBMBS,0 AS ICCWBMBELEG,TRIM(A.ICCWBBART)||'-'||TRIM(A.ICCWBBFARBE)||'-'||A.ICCWBBVAR AS ARTICLE, "
                    + " A.ICCWBBBESCHICH AS ICCWBMBESCHICH,A.ICCWBBDPART AS ICCWBMDPART,'' AS ICCWBMCODE, "
                    + " '' AS ICCWBMNAME,'' AS ICCWBMBDES,'' AS ICCWBMAUFTYP,'' AS ORDERNO,ROUND(SUM(A.ICCWBBISTKLG/1000/0.9144),1) AS STARTMTR, "
                    + " COUNT(A.ICCWBBSTKNR) AS STARTPCNUM,0 AS INMTR,0 AS INPCNUM,0 AS OUTMTR,0 AS OUTPCNUM,0 AS ADJMTR, "
                    + " ROUND(SUM(A.ICCWBBISTKLG/1000/0.9144),1) AS ENDMTR,COUNT(A.ICCWBBSTKNR) AS ENDPCNUM "
                    + " from icc_wbbst A where A.ICCWBBDAT = '" + WBSRTDATE + "'"
                    + " AND A.ICCWBBWAKZ = 'F' AND A.ICCWBBLB <> 'HZ' AND NOT EXISTS( "
                    + " SELECT '1' FROM ICC_WBMOV B WHERE B.ICCWBMWAKZ = 'F' AND A.ICCWBBWAKZ = B.ICCWBMWAKZ AND A.ICCWBBART = B.ICCWBMART "
                    + " AND A.ICCWBBVAR = B.ICCWBMVAR AND A.ICCWBBFARBE = B.ICCWBMFARBE "
                    + " AND A.ICCWBBBESCHICH = B.ICCWBMBESCHICH  AND A.ICCWBBDPART = B.ICCWBMDPART "
                    + " AND B.ICCWBMDAT >='" + WBSRTDATE + "' AND B.ICCWBMDAT <='" + WBENTDATE + "') "
                    + " GROUP BY A.ICCWBBDAT,A.ICCWBBWAKZ,TRIM(A.ICCWBBART)||'-'||TRIM(A.ICCWBBFARBE)||'-'||A.ICCWBBVAR, "
                    + " A.ICCWBBBESCHICH,A.ICCWBBDPART "
                    + " UNION ALL "
                    + " SELECT A.ICCWBMDAT,A.ICCWBMTYPE,A.ICCWBMWAKZ,A.ICCWBMBS,MAX(A.ICCWBMBELEG) AS ICCWBMBELEG, "
                    + "  TRIM(A.ICCWBMART)||'-'||TRIM(A.ICCWBMFARBE)||'-'||A.ICCWBMVAR AS ARTICLE, "
                    + "  A.ICCWBMBESCHICH,A.ICCWBMDPART,A.ICCWBMCODE,A.ICCWBMNAME,A.ICCWBMBDES,A.ICCWBMAUFTYP, "
                    + "  A.ICCWBMAUFKRS||'-'||TO_NUMBER(A.ICCWBMAUFLFD)||'-'||TO_NUMBER(A.ICCWBMAUFPOS) AS ORDERNO, "
                    + "  0 AS STARTMTR, 0 AS STARTPCNUM, "
                    + "  nvl(DECODE(A.ICCWBMTYPE,'IN ',ROUND(SUM(A.ICCWBMMTR/0.9144/1000),1),0),0) AS INMTR, "
                    + "  nvl(DECODE(A.ICCWBMTYPE,'IN ',SUM(CASE WHEN ICCWBMMTR <0 THEN -1 ELSE 1 END),0),0) AS INPCNUM, "
                    + "  nvl(DECODE(A.ICCWBMTYPE,'OUT',ROUND(SUM(A.ICCWBMMTR/0.9144/1000),1),0),0) AS OUTMTR, "
                    + "  nvl(DECODE(A.ICCWBMTYPE,'OUT',SUM(CASE WHEN ICCWBMMTR <0 THEN -1 ELSE 1 END),0),0) AS OUTPCNUM, "
                    + "  nvl(DECODE(A.ICCWBMTYPE,'ADJ',ROUND(SUM(A.ICCWBMMTR/0.9144/1000),1),0),0) AS ADJMTR, "
                    + "  0 AS ENDMTR,0 AS ENDPCNUM "
                    + " FROM ICC_WBMOV A "
                    + " WHERE A.ICCWBMWAKZ = 'F' AND A.ICCWBMDAT >='" + WBSRTDATE + "' AND A.ICCWBMDAT <='" + WBENTDATE + "'"
                    + " GROUP BY A.ICCWBMDAT,A.ICCWBMTYPE,A.ICCWBMWAKZ,A.ICCWBMBS, "
                    + "  TRIM(A.ICCWBMART)||'-'||TRIM(A.ICCWBMFARBE)||'-'||A.ICCWBMVAR, "
                    + "  A.ICCWBMBESCHICH,A.ICCWBMDPART,A.ICCWBMCODE,A.ICCWBMNAME,A.ICCWBMBDES,A.ICCWBMAUFTYP, "
                    + "  A.ICCWBMAUFKRS||'-'||TO_NUMBER(A.ICCWBMAUFLFD)||'-'||TO_NUMBER(A.ICCWBMAUFPOS) "
                    + "  ORDER BY 6,7,8,1,2 "
                     ;
                #endregion
                setLocal = Connect2Proxy(or1);
                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "ICC_WBMOV";
                    SetDefault(tableLocal);
                    byte[] b;
                    string value;
                    string Column = "";
                    List<DataRow> dltRows = new List<DataRow>(); 
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "ARTICLE";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "ICCWBMBESCHICH";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "ICCWBMDPART";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "ICCWBMNAME";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        BalanceQty(dwTemp); 
                    } 
                    set4Return.Merge(setLocal); 
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }

        private void BalanceQty(DataRow dwICC_WBMOV)
        {
            
            try
            {
                string ICCWBMDAT = dwICC_WBMOV["ICCWBMDAT"].ToString();
                string ICCWBMTYPE = dwICC_WBMOV["ICCWBMTYPE"].ToString().Trim();
                string ICCWBMWAKZ = dwICC_WBMOV["ICCWBMWAKZ"].ToString().Trim();
                string ICCWBMBS = dwICC_WBMOV["ICCWBMBS"].ToString().Trim();
                string ARTICLE = dwICC_WBMOV["ARTICLE"].ToString().Trim();
                string ICCWBMBESCHICH = dwICC_WBMOV["ICCWBMBESCHICH"].ToString().Trim();
                string ICCWBMDPART = dwICC_WBMOV["ICCWBMDPART"].ToString().Trim();
                string ICCWBMCODE = dwICC_WBMOV["ICCWBMCODE"].ToString().Trim();
                string ICCWBMNAME = dwICC_WBMOV["ICCWBMNAME"].ToString().Trim();
                string ICCWBMBDES = dwICC_WBMOV["ICCWBMBDES"].ToString().Trim();
                string ICCWBMAUFTYP = dwICC_WBMOV["ICCWBMAUFTYP"].ToString().Trim();
                string ORDERNO = dwICC_WBMOV["ORDERNO"].ToString().Trim();
                double STARTMTR = System.Math.Round(System.Convert.ToDouble(dwICC_WBMOV["STARTMTR"].ToString()),1);
                int STARTPCNUM = System.Convert.ToInt16(dwICC_WBMOV["STARTPCNUM"].ToString());
                double INMTR = System.Math.Round(System.Convert.ToDouble(dwICC_WBMOV["INMTR"].ToString()),1);
                int INPCNUM = System.Convert.ToInt16(dwICC_WBMOV["INPCNUM"].ToString());
                double OUTMTR = System.Math.Round(System.Convert.ToDouble(dwICC_WBMOV["OUTMTR"].ToString()),1);
                int OUTPCNUM = System.Convert.ToInt16(dwICC_WBMOV["OUTPCNUM"].ToString());
                double ADJMTR = System.Math.Round(System.Convert.ToDouble(dwICC_WBMOV["ADJMTR"].ToString()), 1);
                double ENDMTR = 0;
                int ENDPCNUM = 0;
                DataTable ICC_WBMOV;
                WBInventory wbm = null;
                string expression;
                var query = from WBInventory wbinv in ListWBInventory
                            where wbinv.ARTICLE == ARTICLE
                            && wbinv.FINISH == ICCWBMBESCHICH
                            && wbinv.WBBDPART == ICCWBMDPART
                            select wbinv;
                foreach (WBInventory wbinv in query)
                {
                    wbm = wbinv;
                }
                if (wbm == null)
                {
                    wbm = new WBInventory(ICCWBMDAT, "HL", ICCWBMWAKZ, ARTICLE, ICCWBMBESCHICH, ICCWBMDPART, 0, 0.0);
                    ListWBInventory.Add(wbm);
                }
                dwICC_WBMOV["STARTMTR"] = System.Math.Round((wbm.ENDQTY), 1);
                dwICC_WBMOV["STARTPCNUM"] = wbm.ENDPCNUM;
                int I = wbm.ENDPCNUM;
                wbm.ENDPCNUM = wbm.ENDPCNUM + INPCNUM - OUTPCNUM;
                dwICC_WBMOV["ENDPCNUM"] = wbm.ENDPCNUM;
                wbm.ENDQTY = System.Math.Round((wbm.ENDQTY + INMTR - OUTMTR + ADJMTR), 1);
                dwICC_WBMOV["ENDMTR"] = wbm.ENDQTY;
                if (wbm.ENDQTY == 0)
                {
                    dwICC_WBMOV["ENDPCNUM"] = 0;
                    wbm.ENDPCNUM = 0;
                }
                else if (wbm.ENDQTY > 0 & wbm.ENDPCNUM < 1)
                {
                    dwICC_WBMOV["ENDPCNUM"] = I;
                    wbm.ENDPCNUM = I;
                }
                
                /*if (wbm.STARTPCNUM == 0)
                {
                    dwICC_WBMOV["STARTMTR"] = System.Math.Round((wbm.STARTQTY + wbm.ENDQTY), 1);
                    dwICC_WBMOV["STARTPCNUM"] = wbm.STARTPCNUM + wbm.ENDPCNUM;
                    wbm.ENDPCNUM = wbm.STARTPCNUM + wbm.ENDPCNUM + INPCNUM - OUTPCNUM;
                    dwICC_WBMOV["ENDPCNUM"] = wbm.ENDPCNUM;
                    wbm.ENDQTY = System.Math.Round((wbm.STARTQTY + wbm.ENDQTY + INMTR - OUTMTR + ADJMTR), 1);
                    dwICC_WBMOV["ENDMTR"] = wbm.ENDQTY;
                    if (wbm.ENDQTY == 0)
                    {
                        dwICC_WBMOV["ENDPCNUM"] = 0;
                    }
                }
                else
                {
                    dwICC_WBMOV["STARTMTR"] = System.Math.Round(wbm.ENDQTY, 1);
                    dwICC_WBMOV["STARTPCNUM"] = wbm.ENDPCNUM;
                    wbm.ENDPCNUM = wbm.ENDPCNUM + INPCNUM - OUTPCNUM;
                    dwICC_WBMOV["ENDPCNUM"] = wbm.ENDPCNUM;
                    wbm.ENDQTY = System.Math.Round((wbm.ENDQTY + INMTR - OUTMTR + ADJMTR), 1);
                    dwICC_WBMOV["ENDMTR"] = wbm.ENDQTY;
                    if (wbm.ENDQTY == 0)
                    {
                        dwICC_WBMOV["ENDPCNUM"] = 0;
                    }
                }*/

            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        public DataSet GetCOLORDES(DataSet dsTarget, DataRow dwABPOS)
        {
            string TableName = "COLORDES";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT SUBSTR(SDDATEN,2,30) AS COLORDES FROM SDDAT "
                    + " WHERE SUBSTR(SDKEY,1,2)='00' AND trim(SUBSTR(SDKEY,3,10))=:ABPBESCHICH  AND SDSA = '9998' "
                     ;
                orlReadCommand.Parameters.Add("ABPBESCHICH", OracleType.NVarChar).Value = dwABPOS["WBBBESCHICH"].ToString().Trim();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;

                    byte[] b;
                    string value;
                    string ColumnName = "";
                    foreach (DataRow dw in dsResult.Tables["COLORDES"].Rows)
                    {
                        ColumnName = "COLORDES";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;

                        dwABPOS["COLORDES"] = dw["COLORDES"];
                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet BuildWBBST(DataRow dwUI)
        {
            string progress = "查询完成";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                EventDispatcher.Instance.RaiseStatus("查询开始", StatusEventType.StartProgress);

                EventDispatcher.Instance.RaiseStatus("装载结存表", StatusEventType.RunningProgress);
                LoadICC_WBBST(dwUI);
                tbWBDATINV = setWBDATINV.Tables["ICC_WBMOVSUM"];

                EventDispatcher.Instance.RaiseStatus("装载出入库记录", StatusEventType.RunningProgress);
                setResult = GetRawData(dwUI);
                
                EventDispatcher.Instance.RaiseStatus("计算结存", StatusEventType.RunningProgress);
                setReturn.Merge(setResult);
                EventDispatcher.Instance.RaiseStatus("保存结存表", StatusEventType.RunningProgress);
                //SaveINVQty();
                //setReturn.Merge(setWBDATINV);
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
                #region JAK
                /*
                EventDispatcher.Instance.RaiseStatus("查询开始", StatusEventType.StartProgress);
                setResult = LoadICC_WBBST(dwUI);
                setResult = GetRawData(dwUI);
                setReturn.Merge(setResult);
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
                 * */
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}